﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Tools.Framework;

namespace Common.Framework
{
    public class DbHelper : IDisposable
    {
        /// <summary>
        /// 数据库驱动列表
        /// </summary>
        public enum DbProviderType
        {
            SQLSERVER = 0,
            Oracle = 1
        }

        /// <summary>
        /// 数据库驱动列表
        /// </summary>
        private System.Collections.Generic.Dictionary<int, string> DbProvider = new System.Collections.Generic.Dictionary<int, string>
            {
                {0,"System.Data.SqlClient"},
                {1,"System.Data.OracleClient"}
            };


        /// <summary>
        /// 驱动
        /// </summary>
        private string dbProviderName;   //System.Data.OracleClient
        /// <summary>
        /// 连接
        /// </summary>

        public DbConnection connection;

        public DbCommand dbCommand;



        /// <summary>
        /// 数据库驱动  CreateDbHelper
        /// </summary>
        public DbHelper(string connectionString, DbProviderType dbProviderType, bool isTran = false)
        {
            dbProviderName = DbProvider[(int)dbProviderType];
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(this.dbProviderName);
            connection.ConnectionString = connectionString;
            dbCommand = connection.CreateCommand();
            if (isTran)
                dbCommand.Transaction = dbCommand.Connection.BeginTransaction();
        }



        ///  <summary>
        /// 执行存储过程
        ///  </summary>
        ///  <param name="storedProcedure">存储过程名字</param>
        /// <param name="parameterCollection">参数</param>
        /// <returns>返回受影响的行数</returns>
        public int ExecuteProcCommond(string storedProcedure, DbParameterCollection parameterCollection = null)
        {
            dbCommand.CommandText = storedProcedure;
            dbCommand.CommandType = CommandType.StoredProcedure;
            AddParameterCollection(parameterCollection);

            if (dbCommand.Transaction != null)
            {
                dbCommand.Transaction.Commit();
            }

            return dbCommand.ExecuteNonQuery();
        }


        #region 增加参数
        /// <summary>
        /// 参数增加
        /// </summary>
        /// <param name="dbParameterCollection"></param>
        private void AddParameterCollection(DbParameterCollection dbParameterCollection)
        {
            if (dbParameterCollection != null)
            {
                if (dbParameterCollection.Count > 0)
                {
                    dbCommand.Parameters.Clear();
                    foreach (DbParameter dbParameter in dbParameterCollection)
                    {
                        dbCommand.Parameters.Add(dbParameter);
                    }
                }
            }
        }


        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="parameterName"></param>
        /// <returns></returns>
        public DbParameter GetParameter(string parameterName)
        {
            return dbCommand.Parameters[parameterName];
        }



        #endregion



        #region 执行

        public DataSet ExecuteDataSet(string sql, DbParameterCollection dbParameterCollection = null)
        {

            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(this.dbProviderName);

            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();

            dbCommand.CommandType = CommandType.Text;

            dbCommand.CommandText = sql;

            dbDataAdapter.SelectCommand = dbCommand;

            AddParameterCollection(dbParameterCollection);

            DataSet ds = new DataSet();

            dbDataAdapter.Fill(ds);

            return ds;

        }



        public DataTable ExecuteDataTable(string sql, DbParameterCollection dbParameterCollection = null)
        {

            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(this.dbProviderName);

            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();

            dbCommand.CommandText = sql;

            dbCommand.CommandType = CommandType.Text;

            dbDataAdapter.SelectCommand = dbCommand;

            AddParameterCollection(dbParameterCollection);

            DataTable dataTable = new DataTable();

            dbDataAdapter.Fill(dataTable);

            return dataTable;
        }



        public int ExecuteNonQuery(string sql, DbParameterCollection dbParameterCollection = null)
        {
            try
            {
                dbCommand.Connection.Open();

                dbCommand.CommandText = sql;

                dbCommand.CommandType = CommandType.Text;

                AddParameterCollection(dbParameterCollection);

                int ret = dbCommand.ExecuteNonQuery();

                if (dbCommand.Transaction != null)
                {
                    dbCommand.Transaction.Commit();
                }

                dbCommand.Connection.Close();

                return ret;
            }
            catch (Exception ex)
            {
                dbCommand.Transaction.Rollback();
                LogHelper.Write(ex);
                return 0;
            }
        }



        public object ExecuteScalar(string strsql, DbParameterCollection dbParameterCollection = null)
        {

            try
            {
                dbCommand.Connection.Open();

                dbCommand.CommandText = strsql;
                dbCommand.CommandType = CommandType.Text;

                AddParameterCollection(dbParameterCollection);

                object ret = dbCommand.ExecuteScalar();

                if (dbCommand.Transaction != null)
                {
                    dbCommand.Transaction.Commit();
                }

                dbCommand.Connection.Close();

                return ret;
            }
            catch (Exception ex)
            {
                dbCommand.Transaction.Rollback();
                LogHelper.Write(ex);
                return 0;
            }
        }

        #endregion

        public void Dispose()
        {
            if (dbCommand.Connection.State == ConnectionState.Open)
            {
                dbCommand.Connection.Close();
            }
        }
    }
}
